import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        /**
         * 完整的目标是执行select * from student_1118;
         * */
        //2、建立数据库链接

        //写明Mysql服务端所在地
        //以后写项目，只需要修改默认数据库名称即可
        String deafultDatabaseName = "testbase";//设置默认数据库   和mysql中使用 use testbase;一样
        String password = "missh111";//填写自己设置的mysql密码

        //下面这行基本不变
        String user = "root";//用户名
        String url = "jdbc:mysql://127.0.0.1:3306/" + deafultDatabaseName + "?useSSL=false&characterEncoding=utf8";

        Connection connection = DriverManager.getConnection(url, user, password);

        //打印connection对象，测试看是否连接成功
        System.out.println(connection);

        //queryDemo(connection);
        updateDemo(connection);

        //-1、关闭刚才的链接
        connection.close();
    }


    //3、执行sql语句(有返回结果和无返回结果)
    private static void updateDemo(Connection connection) throws SQLException {
        //无返回结果
        //获取Statement对象
        Statement statement=connection.createStatement();
        String sql="insert into student_1118(sn,name,sex) values (20201119,'Miss.lynn','女')";
        int affectedRows=statement.executeUpdate(sql);
        System.out.printf("Query OK,%d row affacted%n",affectedRows);
        statement.close();
    }

    private static void queryDemo(Connection connection) throws SQLException {
        //有返回的结果
        Statement statement=connection.createStatement();
        String sql="select * from student_1118";  //这里写不写分号都行
        ResultSet resultSet = statement.executeQuery(sql);
        //1、statement.executeQuery(...);用在查询等有返回值的场景下
        //2、ResultSet 代表的是"结果集"的抽象
        //3、Statement 代表的是"语句"的抽象
        int count=0;
        System.out.println("+----+------+-----------+-----+");
        System.out.println("| id | sn   | name      | sex |");
        System.out.println("+----+------+-----------+-----+");
        while(resultSet.next()){
            String id=resultSet.getString(1);
            String sn=resultSet.getString(2);
            String name=resultSet.getString(3);
            String sex=resultSet.getString(4);

            System.out.format("| %2s | %4s | %-7s| %2s |%n",id,sn,name,sex);
            count++;
        }
        System.out.println("+----+------+-----------+-----+");
        System.out.format("%2s row in set.",count);

        //-3、关闭刚才的链接
        resultSet.close();
        //-2、关闭刚才的链接
        statement.close();
    }
}
